package com.learn.mybatisjdbc.dao;

import com.learn.mybatisjdbc.pojo.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.List;

public class UserJDBCDao {
    public List<User> findUserListByGtAge(int age){
        /**
         * 1.加载JDBC驱动；
         */
        String url = "jdbc:mysql://localhost:3306/students?serverTimezone=UTC&characterEncoding=utf-8";
        String username = "root";
        String password = "12345678";
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 1.加载JDBC驱动；
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2.建立并获取数据库连接
            connection = DriverManager.getConnection(url, username, password);
            // 3.创建 JDBC Statements 对象

            // 4.设置SQL语句的传入参数
            String sql = "select * from user where age > ?";
            preparedStatement = connection.prepareStatement(sql);
            // Params: 传递给sql使用的参数
            // parameterIndex – the first parameter is 1, the second is 2
            preparedStatement.setInt(1, age);
            // 5：执行SQL语句并获得查询结果， 查询出来是 set 数据类型
            resultSet = preparedStatement.executeQuery();
            // 6.对查询结果进行转换处理并将处理结果返回
            List<User> userList = new ArrayList<>();
            while (resultSet.next()){
                long id = resultSet.getLong("id");
                String username1 = resultSet.getString("username");
                int age1 = resultSet.getInt("age");
                String id_card = resultSet.getString("id_card");
                User user = new User();
                user.setId(id);
                user.setUsername(username1);
                user.setAge(age1);
                user.setIdCard(id_card);
                userList.add(user);
            }
            return userList;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try{
                // 7.释放相关资源（关闭Connection，关闭Statement，关闭ResultSet）
                if(resultSet!=null){
                    resultSet.close();
                }
                if(preparedStatement!=null){
                    preparedStatement.close();
                }
                if(connection!=null){
                    connection.close();
                }
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        // 没有数据，返回空的list
        return Collections.emptyList();
    }
}
